//
//  DataBaseSingTon.m
//  newDouban
//
//  Created by dllo on 15/11/3.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBaseSingTon.h"
#import <sqlite3.h>


@implementation DataBaseSingTon

+ (instancetype)shareDataBase
{
    static DataBaseSingTon *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBaseSingTon alloc] init];
    }
    return dataBase;
}


//声明数据库对象
static sqlite3 *db = nil;  //(sqlite3无联想,硬打)
//sqlite数据库是基于c语言的底层数据库

//打开数据库()
- (void)openDB
{
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    /**
     *打开数据库
     参数1,数据库文件路径
     参数2.数据库对象
     */
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"cinema.sqlite"];
    
    NSLog(@"sqlPath = %@", sqlPath);
    
    BOOL result = sqlite3_open(sqlPath.UTF8String, &db);//成功返回的是零
    //SQLITE_OK相当于内部的零
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"失败");
    }
}


//创建表
- (void)createTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS lanou0920(number INTEGER PRIMARY KEY AUTOINCREMENT, cinemaName TEXT, address TEXT, telephone TEXT)";
    BOOL result1 = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result1 == SQLITE_OK) {
        NSLog(@"创建表成功");
    } else {
        NSLog(@"创建表失败");
    }
}



//打开数据库()
- (void)openDBAC
{
    if (db != nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    /**
     *打开数据库
     参数1,数据库文件路径
     参数2.数据库对象
     */
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"activity.sqlite"];
    
    NSLog(@"activity.sqlite.sqlPath = %@", sqlPath);
    
    BOOL result = sqlite3_open(sqlPath.UTF8String, &db);//成功返回的是零
    //SQLITE_OK相当于内部的零
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    } else {
        NSLog(@"失败");
    }
}





//创建表
// 创建活动表
- (void)createActivityTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activity(number INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, name TEXT, image TEXT, address TEXT, category_name TEXT, begin_time TEXT, end_time TEXT, title TEXT, wisher_countt TEXT, participant_countt TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
        if (SQLITE_OK == result) {
            NSLog(@"创建表成功");
        } else {
            NSLog(@"创建表失败");
        }
}

//插入
- (void)insertActivity:(Activity *)act
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO activity(content, name, image, address, category_name, begin_time, end_time, title, wisher_countt, participant_countt) VALUES('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", act.content, act.name, act.image, act.address, act.category_name, act.begin_time, act.end_time, act.title, act.wisher_countt, act.participant_countt];
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
        if (SQLITE_OK == result) {
            NSLog(@"插入成功");
        } else {
            NSLog(@"插入失败");
        }
}


//删除数据
- (void)deleteActivity:(NSString *)sql
{
    //NSString *sql = [NSString stringWithFormat:@"DELETE FROM lanou WHERE name = '%@'", name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除数据将建军节成功");
    } else {
        NSLog(@"删除数据将建军节间距失败");
    }
    
}



//查询
- (NSMutableArray *)selectActivity:(NSString *)sql
{
    NSMutableArray *array = [NSMutableArray array];
    //NSString *sql = @"SELECT * FROM activity";
    
    //    NSString *sql = @"SELECT * FROM activity";
    // 创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    // 查询前的准备工作
    // 参数3. 限制sql语句长度, -1为不限制
    // 将数据库对象db, sql语句, 跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (SQLITE_OK == result) {
        NSLog(@"查询准备成功");
    } else {
        NSLog(@"查询准备失败");
    }
    // 循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 取出第一列数据
        

        const unsigned char *content = sqlite3_column_text(stmt, 1);
        const unsigned char *name = sqlite3_column_text(stmt, 2);
        const unsigned char *image = sqlite3_column_text(stmt, 3);
        const unsigned char *address = sqlite3_column_text(stmt, 4);
        const unsigned char *category_name = sqlite3_column_text(stmt, 5);
        const unsigned char *begin_time = sqlite3_column_text(stmt, 6);
        const unsigned char *end_time = sqlite3_column_text(stmt, 7);
        const unsigned char *title = sqlite3_column_text(stmt, 8);
        const unsigned char *wisher_countt = sqlite3_column_text(stmt, 9);
        const unsigned char *participant_countt = sqlite3_column_text(stmt, 10);
        
        
        // 将语言类型转成OC类型
        NSString *contentStr = [NSString stringWithUTF8String:(const char*)content];
        NSString *nameStr = [NSString stringWithUTF8String:(const char*)name];
        NSString *imageStr = [NSString stringWithUTF8String:(const char*)image];
        NSString *addressStr = [NSString stringWithUTF8String:(const char*)address];
        NSString *category_nameStr = [NSString stringWithUTF8String:(const char*)category_name];
        NSString *begin_timeStr = [NSString stringWithUTF8String:(const char*)begin_time];
        NSString *end_timeStr = [NSString stringWithUTF8String:(const char*)end_time];
        //NSString *begin_timeStr = [NSString stringWithUTF8String:(const char*)begin_time];
        NSString *titleStr = [NSString stringWithUTF8String:(const char*)title];
        NSString *wisher_counttStr = [NSString stringWithUTF8String:(const char*)wisher_countt];
        NSString *participant_counttStr = [NSString stringWithUTF8String:(const char*)participant_countt];
        
        // 创建学生对象
        Activity *act = [[Activity alloc] init];
        act.content = contentStr;
        act.name = nameStr;
        act.image = imageStr;
        act.address = addressStr;
        act.category_name = category_nameStr;
        act.begin_time = begin_timeStr;
        act.end_time = end_timeStr;
        act.title = titleStr;
        act.wisher_countt = wisher_counttStr;
        act.participant_countt = participant_counttStr;
        
        [array addObject:act];
        
    }
    
    return array;
    

}



//插入一个电影model
- (void)insertCinema:(Cinema *)cinema
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO lanou0920(cinemaName, address, telephone)VALUES('%@', '%@', '%@')", cinema.cinemaName, cinema.address, cinema.telephone];
    //执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
}


//查询所有学生
- (NSMutableArray *)selectCinema
{
    
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = @"SELECT * FROM lanou0920";
    
    //创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    //查询前的准备工作
    //参数3.设置限制sql语句长度,-1 为不限制
    //将数据库对象db,sql语句,跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    } else {
        NSLog(@"查询失败");
    }
    
    //循环遍历每一行数据(用指针指向每一行)
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        //取出第一列数据
        int number = sqlite3_column_int(stmt, 0);
        //取出第二列数据
        const unsigned char * cinemaName = sqlite3_column_text(stmt, 1);//点进去看该返回什么数据类型
        const unsigned char * address = sqlite3_column_text(stmt, 2);
        const unsigned char * telephone = sqlite3_column_text(stmt, 3);
        
        
        //将c予以类型转成oc类型
        //
        NSInteger num = number;
        NSString *cinemaNameStr = [NSString stringWithUTF8String:(const char *)cinemaName];
        NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
        NSString *telephoneStr = [NSString stringWithUTF8String:(const char *)telephone];
        
        
        Cinema *cinema = [[Cinema alloc] init];
        cinema.number = num;
        cinema.cinemaName = cinemaNameStr;
        cinema.address = addressStr;
        cinema.telephone = telephoneStr;
        
        
        //创建学生对象
//        Student *stu = [[Student alloc] init];
//        stu.name = nameStr;
//        stu.gender = genderStr;
//        stu.number = num;
//        stu.age = age2;
//        
//        
        [array addObject:cinema];
        [cinema release];
    }
    
    return array;
    
}



//删除整张表
- (void)dropTable
{
    NSString *sql = @"DROP TABLE lanou0920";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    } else {
        NSLog(@"删除表失败");
    }
}


- (void)dropTableAC
{
    NSString *sql = @"DROP TABLE activity";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    } else {
        NSLog(@"删除表失败");
    }
}

- (void)dropTableAC:(NSString *)sql
{
    //NSString *sql = @"DROP TABLE activity";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    } else {
        NSLog(@"删除表失败");
    }
}


/**
 *  
 */


// 创建活动收藏列表
- (void)createActivityStoreTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activityStore(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, userName TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    //        if (SQLITE_OK == result) {
    //            NSLog(@"创建表成功");
    //        } else {
    //            NSLog(@"创建表失败");
    //        }
}
- (void)insertActivitysql:(NSString *)sql
{
    //NSString *sql = [NSString stringWithFormat:@"INSERT INTO activityStore(title, userName) VALUES('%@', '%@')", actS.title, actS.userName];
    // 执行sql语句
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        NSLog(@"活动收藏插入成功");
        
    } else {
        NSLog(@"活动收藏插入失败");
    }
}
// 查询活动收藏列表
- (NSMutableArray *)selectActivityStoreSQL:(NSString *)sql;
{
    NSMutableArray *array = [NSMutableArray array];
    
    
    //    NSString *sql = @"SELECT * FROM activityStore";
    // 创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    NSLog(@"%d", result);
    // 循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 取出第一列数据
        
        const unsigned char *title1 = sqlite3_column_text(stmt, 1);
        const unsigned char *userName1 = sqlite3_column_text(stmt, 2);
        
        
        // 将语言类型转成OC类型
        NSString *title = [NSString stringWithUTF8String:(const char*)title1];
        NSString *userName = [NSString stringWithUTF8String:(const char*)userName1];
        
        
        ActivityStore *actS = [[ActivityStore alloc] init];
        actS.title = title;
        actS.userName = userName;
        
        [array addObject:actS];
        
    }
    
    return array;
    
}



- (void)createUserTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS user(number INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT, passWord TEXT, result TEXT)";
    sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
}
- (void)insertUser:(User *)user
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO user(userName, passWord, result) VALUES('%@', '%@', '%@')", user.userName, user.passWord, user.result];
    sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
}
- (NSMutableArray *)selectUserSQL:(NSString *)sql
{
    
    NSMutableArray *array = [NSMutableArray array];
    sqlite3_stmt *stmt = nil;
    sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 取出第一列数据
        const unsigned char *userName = sqlite3_column_text(stmt, 1);
        const unsigned char *passWord = sqlite3_column_text(stmt, 2);
        const unsigned char *result = sqlite3_column_text(stmt, 3);

        
        NSString *userStr = [NSString stringWithUTF8String:(const char*)userName];
        NSString *passStr = [NSString stringWithUTF8String:(const char*)passWord];
        NSString *resultStr = [NSString stringWithUTF8String:(const char*)result];
      
        
        User *user = [[User alloc] init];
        user.userName = userStr;
        user.passWord = passStr;
        user.result = resultStr;

//        [array addObject:user];
        [array addObject:user.userName];
        [user release];
    }
    return array;
}


- (void)updateUser:(NSString *)sql
{
    
    //NSString *sql = [NSString stringWithFormat:@"UPDATE lanou SET name = '%@', gender = '%@', age = '%ld' WHERE name = '%@'", stu.name, stu.gender, stu.age, name];
    sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
}




@end
